/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.database;

import br.com.models.ClassRoom;
import br.com.models.Discipline;
import br.com.models.Teacher;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author Valdigleis
 */
public class DaoClassRoom {
    
    private String sql;
    
    private Connection connection;

    /**
     * Método construtor para a classe DaoClassRoom
     * que faz a comunicação com a entidade que representa
     * as turmas na base de dados.
     */
    public DaoClassRoom() {
        this.connection = Derby.getConnection();
    }
    
    public void saveClassRoom(ClassRoom classRoom){
        try{
            sql = "INSERT INTO SAD.CLASSROOM(teacher, discipline, number, semester)"
                   + "values (?,?,?,?)";
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setInt(1, classRoom.getTeacher().getId());
            stmt.setInt(2, classRoom.getDiscipline().getId());
            stmt.setInt(3, classRoom.getNumber());
            stmt.setString(4, classRoom.getSemester());
            stmt.execute();
            stmt.close();
        }catch(SQLException e){
            System.err.println("Erro ao salvar a turma!");
            System.out.println(e.getMessage());
        }
    }
    
    public ArrayList<ClassRoom> seacherNumberClass(int idDiscipline){
        try{
            ArrayList<ClassRoom> listclassRoom = new ArrayList<ClassRoom>();
            sql = "SELECT * FROM SAD.CLASSROOM WHERE discipline=?";
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setInt(1, idDiscipline);
            ResultSet rs = stmt.executeQuery();
            while(rs.next()){
                ClassRoom classRoom = new ClassRoom();
                Teacher teacher = new Teacher();
                Discipline discipline = new Discipline();
                classRoom.setId(rs.getInt("id"));
                teacher.setId(rs.getInt("teacher"));
                discipline.setId(rs.getInt("discipline"));
                classRoom.setTeacher(teacher);
                classRoom.setDiscipline(discipline);
                listclassRoom.add(classRoom);
            }
            rs.close();
            stmt.close();
            return listclassRoom;
        }catch(SQLException e){
            System.err.println("Erro ao salvar a pre-requisitos!");
            System.out.println(e.getMessage());
        }
        return null;
    }
    
    public ArrayList<ClassRoom> seacherByTheTeacher(int IdTeacher){
        try{
            ArrayList<ClassRoom> listclassRoom = new ArrayList<ClassRoom>();
            sql = "SELECT * FROM SAD.CLASSROOM WHERE teacher=? ORDER BY semester DESC";
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setInt(1, IdTeacher);
            ResultSet rs = stmt.executeQuery();
            while(rs.next()){
                ClassRoom classRoom = new ClassRoom();
                Teacher teacher = new Teacher();
                Discipline discipline = new Discipline();
                classRoom.setId(rs.getInt("id"));
                teacher.setId(rs.getInt("teacher"));
                discipline.setId(rs.getInt("discipline"));
                classRoom.setTeacher(teacher);
                classRoom.setDiscipline(discipline);
                classRoom.setNumber(rs.getInt("number"));
                classRoom.setSemester(rs.getString("semester"));
                listclassRoom.add(classRoom);
            }
            rs.close();
            stmt.close();
            return listclassRoom;
        }catch(SQLException e){
            System.err.println("Erro ao salvar a pre-requisitos!");
            System.out.println(e.getMessage());
        }
        return null;
    }
    
    public ArrayList<ClassRoom> seacherByTheDiscipline(int IdDiscipline){
        try{
            ArrayList<ClassRoom> listclassRoom = new ArrayList<ClassRoom>();
            sql = "SELECT * FROM SAD.CLASSROOM WHERE discipline=? ORDER BY semester DESC";
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setInt(1, IdDiscipline);
            ResultSet rs = stmt.executeQuery();
            while(rs.next()){
                ClassRoom classRoom = new ClassRoom();
                Teacher teacher = new Teacher();
                Discipline discipline = new Discipline();
                classRoom.setId(rs.getInt("id"));
                teacher.setId(rs.getInt("teacher"));
                discipline.setId(rs.getInt("discipline"));
                classRoom.setTeacher(teacher);
                classRoom.setDiscipline(discipline);
                classRoom.setNumber(rs.getInt("number"));
                classRoom.setSemester(rs.getString("semester"));
                listclassRoom.add(classRoom);
            }
            rs.close();
            stmt.close();
            return listclassRoom;
        }catch(SQLException e){
            System.err.println("Erro ao salvar a pre-requisitos!");
            System.out.println(e.getMessage());
        }
        return null;
    }
    
    public ArrayList<ClassRoom> seacherByTheDisciplineAndSemster(int IdDiscipline, String semester){
        try{
            ArrayList<ClassRoom> listclassRoom = new ArrayList<ClassRoom>();
            sql = "SELECT * FROM SAD.CLASSROOM WHERE discipline=? AND semester=? ORDER BY semester DESC";
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setInt(1, IdDiscipline);
            stmt.setString(2, semester);
            ResultSet rs = stmt.executeQuery();
            while(rs.next()){
                ClassRoom classRoom = new ClassRoom();
                Teacher teacher = new Teacher();
                Discipline discipline = new Discipline();
                classRoom.setId(rs.getInt("id"));
                teacher.setId(rs.getInt("teacher"));
                discipline.setId(rs.getInt("discipline"));
                classRoom.setTeacher(teacher);
                classRoom.setDiscipline(discipline);
                classRoom.setNumber(rs.getInt("number"));
                classRoom.setSemester(rs.getString("semester"));
                listclassRoom.add(classRoom);
            }
            rs.close();
            stmt.close();
            return listclassRoom;
        }catch(SQLException e){
            System.err.println("Erro ao salvar a pre-requisitos!");
            System.out.println(e.getMessage());
        }
        return null;
    }
    
    
    public ArrayList<ClassRoom> seacherByTheDisciplineSemestre(int IdDiscipline, String semester){
        try{
            ArrayList<ClassRoom> listclassRoom = new ArrayList<ClassRoom>();
            sql = "SELECT * FROM SAD.CLASSROOM WHERE discipline=? AND Semester=? ORDER BY semester DESC";
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setInt(1, IdDiscipline);
            stmt.setString(2, semester);
            ResultSet rs = stmt.executeQuery();
            while(rs.next()){
                ClassRoom classRoom = new ClassRoom();
                Teacher teacher = new Teacher();
                Discipline discipline = new Discipline();
                classRoom.setId(rs.getInt("id"));
                teacher.setId(rs.getInt("teacher"));
                discipline.setId(rs.getInt("discipline"));
                classRoom.setTeacher(teacher);
                classRoom.setDiscipline(discipline);
                classRoom.setNumber(rs.getInt("number"));
                classRoom.setSemester(rs.getString("semester"));
                listclassRoom.add(classRoom);
            }
            rs.close();
            stmt.close();
            return listclassRoom;
        }catch(SQLException e){
            System.err.println("Erro ao salvar a pre-requisitos!");
            System.out.println(e.getMessage());
        }
        return null;
    }
    
    public ArrayList<ClassRoom> seacherByTheTeacherAndDiscipline(int IdTeacher, int IdDiscipline){
        try{
            ArrayList<ClassRoom> listclassRoom = new ArrayList<ClassRoom>();
            sql = "SELECT * FROM SAD.CLASSROOM WHERE teacher=? AND discipline=? ORDER BY semester DESC";
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setInt(1, IdTeacher);
            stmt.setInt(2, IdDiscipline);
            ResultSet rs = stmt.executeQuery();
            while(rs.next()){
                ClassRoom classRoom = new ClassRoom();
                Teacher teacher = new Teacher();
                Discipline discipline = new Discipline();
                classRoom.setId(rs.getInt("id"));
                teacher.setId(rs.getInt("teacher"));
                discipline.setId(rs.getInt("discipline"));
                classRoom.setTeacher(teacher);
                classRoom.setDiscipline(discipline);
                classRoom.setNumber(rs.getInt("number"));
                classRoom.setSemester(rs.getString("semester"));
                listclassRoom.add(classRoom);
            }
            rs.close();
            stmt.close();
            return listclassRoom;
        }catch(SQLException e){
            System.err.println("Erro ao salvar a pre-requisitos!");
            System.out.println(e.getMessage());
        }
        return null;
    }
   
    public ArrayList<ClassRoom> seacherAll(String semester){
        try{
            ArrayList<ClassRoom> listclassRoom = new ArrayList<ClassRoom>();
            sql = "SELECT * FROM SAD.CLASSROOM WHERE semester=? ";
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setString(1, semester);
            ResultSet rs = stmt.executeQuery();
            while(rs.next()){
                ClassRoom classRoom = new ClassRoom();
                Teacher teacher = new Teacher();
                Discipline discipline = new Discipline();
                classRoom.setId(rs.getInt("id"));
                teacher.setId(rs.getInt("teacher"));
                discipline.setId(rs.getInt("discipline"));
                classRoom.setTeacher(teacher);
                classRoom.setDiscipline(discipline);
                classRoom.setNumber(rs.getInt("number"));
                classRoom.setSemester(rs.getString("semester"));
                listclassRoom.add(classRoom);
            }
            rs.close();
            stmt.close();
            return listclassRoom;
        }catch(SQLException e){
            System.err.println("Erro ao salvar a pre-requisitos!");
            System.out.println(e.getMessage());
        }
        return null;
    }
    
    
    public void remove(ClassRoom classRoom){
        try{
            sql = "DELETE FROM SAD.CLASSROOM WHERE id=?";
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setInt(1, classRoom.getId());
            stmt.execute();
            stmt.close();
        }catch(SQLException e){
            System.err.println("Erro ao excluir o expediente!");
            System.out.println(e.getMessage());
        }
    }
    
    
    public void removeByTeacher(Teacher teacher){
        try{
            sql = "DELETE FROM SAD.CLASSROOM WHERE teacher=?";
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setInt(1, teacher.getId());
            stmt.execute();
            stmt.close();
        }catch(SQLException e){
            System.err.println("Erro ao excluir o expediente!");
            System.out.println(e.getMessage());
        }
    }
    
    public void removeByTeacher(Discipline discipline){
        try{
            sql = "DELETE FROM SAD.CLASSROOM WHERE discipline=?";
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setInt(1, discipline.getId());
            stmt.execute();
            stmt.close();
        }catch(SQLException e){
            System.err.println("Erro ao excluir o expediente!");
            System.out.println(e.getMessage());
        }
    }
    
    
    public boolean checkNumberClass(Discipline discipline, int number){
        try{
            sql = "SELECT * FROM SAD.CLASSROOM WHERE  discipline=? AND number=?";
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setInt(1, discipline.getId());
            stmt.setInt(2, number);
            ResultSet rs = stmt.executeQuery();
            while(rs.next()){
                rs.close();
                stmt.close();
                return true;
            }
            rs.close();
            stmt.close();
            return false;
        }catch(SQLException e){
            System.err.println("Erro ao salvar a pre-requisitos!");
            System.out.println(e.getMessage());
        }
        return false;
    }
    
    public boolean checkExisteceClass(Discipline disc, String semester){
        try{
            ArrayList<ClassRoom> listclassRoom = new ArrayList<ClassRoom>();
            sql = "SELECT * FROM SAD.CLASSROOM WHERE discipline=? AND semester=? ";
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setInt(1, disc.getId());
            stmt.setString(2, semester);
            ResultSet rs = stmt.executeQuery();
            while(rs.next()){
                ClassRoom classRoom = new ClassRoom();
                Teacher teacher = new Teacher();
                Discipline discipline = new Discipline();
                classRoom.setId(rs.getInt("id"));
                teacher.setId(rs.getInt("teacher"));
                discipline.setId(rs.getInt("discipline"));
                classRoom.setTeacher(teacher);
                classRoom.setDiscipline(discipline);
                classRoom.setNumber(rs.getInt("number"));
                classRoom.setSemester(rs.getString("semester"));
                listclassRoom.add(classRoom);
            }
            if(listclassRoom.isEmpty()){
                rs.close();
                stmt.close();
                closeConnection();
                return true;
            }
            rs.close();
            stmt.close();
            closeConnection();
            return false;
        }catch(SQLException e){
            System.err.println("Erro ao salvar a pre-requisitos!");
            System.out.println(e.getMessage());
        }
        return false;
    }
    
    /**
     * Método que finaliza a conexão com a base de dados.
     */
    public void closeConnection(){
        try{
            this.connection.close();
        }catch(SQLException e){
            System.err.println("Erro tentar fechar a conexão!");
            System.out.println(e.getMessage());
        }
    }
    
}
